﻿/*
 * @file: UsersDA.cs
 * @in namespace: DAL
 */
using System;
using System.Data;
using System.Data.SqlClient;

namespace DAL
{
    public class UsersDA : BaseDAL, IUsers
    {
        private const string tableName = "Users";
        private const string userID = "UserID";
        private const string roleID = "RoleID";
        //private const string userName = "UserName";
        private const string email = "Email";
        private const string password = "Password";
        private const string createDate = "CreateDate";
        private const string firtsName = "FirstName";
        private const string lastName = "LastName";
        private const string gender = "Gender";
        private const string birthday = "Birthday";
        private const string address = "Address";
        private const string phone = "Phone";
        private const string secretQuestion = "SecretQuestion";
        private const string secretAnswer = "SecretAnswer";
        private const string isEnable = "IsEnable";

        public UsersDA()
        {
            //
            // TODO: Add constructor logic here
            //
        }

        #region IUsers Members

        //public static int UserNameExisted(string _userName)
        //{
        //    try
        //    {
        //        return RecordExisted(tableName, userName, _userName);
        //    }
        //    catch (Exception ex)
        //    {
        //        throw ex;
        //    }
        //}

        public static int EmailExisted(string _email)
        {
            try
            {
                return RecordExisted(tableName, email, _email);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /*
         * @method: GetUsers
         * @description: get all Users
         * @input: allowFilter <TRUE enables ignore hidden records>
         * @output: records corresponding
         */
        public Users[] GetUsers(bool allowFilter)
        {
            Users[] result = null;
            try
            {
                SqlCommand cmd = new SqlCommand();
                cmd.CommandType = CommandType.Text;
                if (allowFilter)
                    cmd.CommandText = String.Format("SELECT * FROM {0} WHERE {1} = 1",
                        tableName, isEnable);
                else
                    cmd.CommandText = String.Format("SELECT * FROM {0}", tableName);

                String[] columnNames = {    userID,
                                            roleID,
                                            email,
                                            password,
                                            createDate,
                                            firtsName,
                                            lastName,
                                            gender,
                                            birthday,
                                            address,
                                            phone,
                                            secretQuestion,
                                            secretAnswer,
                                            isEnable};
                result = SelectCollection<Users>(columnNames, columnNames, cmd);

            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }

        /*
         * @method: GetUserByUserName
         * @description: get a user by user name
         * @input: userName, allowFilter <TRUE enables ignore hidden records>
         * @output: record corresponding
         */
        public Users GetUser(string _email, bool allowFilter)
        {
            Users[] result = null;
            try
            {
                SqlCommand cmd = new SqlCommand();
                cmd.CommandType = CommandType.Text;
                if (allowFilter)
                {
                    cmd.CommandText = String.Format("SELECT * FROM {0} WHERE {1} = @{2} AND {3} = 1",
                        tableName, email, email, isEnable);
                    cmd.Parameters.Add(String.Format("@{0}", email), SqlDbType.NVarChar).Value = _email;
                }
                else
                {
                    cmd.CommandText = String.Format("SELECT * FROM {0} WHERE {1} = @{2}",
                        tableName, email, email);
                    cmd.Parameters.Add(String.Format("@{0}", email), SqlDbType.NVarChar).Value = _email;
                }

                String[] columnNames = {    userID,
                                            roleID,
                                            email,
                                            password,
                                            createDate,
                                            firtsName,
                                            lastName,
                                            gender,
                                            birthday,
                                            address,
                                            phone,
                                            secretQuestion,
                                            secretAnswer,
                                            isEnable};
                result = SelectCollection<Users>(columnNames, columnNames, cmd);

            }
            catch (Exception ex)
            {
                throw ex;
            }
            if (result.Length > 0)
                return result[0];
            else
                return null;
        }

        /*
         * @method: GetUserByUserID
         * @description: get a user by user ID
         * @input: userID, allowFilter <TRUE enables ignore hidden records>
         * @output: record corresponding
         */
        public Users GetUser(int _userID, bool allowFilter)
        {
            Users[] result = null;
            try
            {
                SqlCommand cmd = new SqlCommand();
                cmd.CommandType = CommandType.Text;
                if (allowFilter)
                {
                    cmd.CommandText = String.Format("SELECT * FROM {0} WHERE {1} = @{2} AND {3} = 1",
                        tableName, userID, userID, isEnable);
                    cmd.Parameters.Add(String.Format("@{0}", userID), SqlDbType.Int).Value = _userID;
                }
                else
                {
                    cmd.CommandText = String.Format("SELECT * FROM {0} WHERE {1} = @{2}",
                        tableName, userID, userID);
                    cmd.Parameters.Add(String.Format("@{0}", userID), SqlDbType.Int).Value = _userID;
                }

                String[] columnNames = {    userID,
                                            roleID,
                                            email,
                                            password,
                                            createDate,
                                            firtsName,
                                            lastName,
                                            gender,
                                            birthday,
                                            address,
                                            phone,
                                            secretQuestion,
                                            secretAnswer,
                                            isEnable};
                result = SelectCollection<Users>(columnNames, columnNames, cmd);

            }
            catch (Exception ex)
            {
                throw ex;
            }
            if (result.Length > 0)
                return result[0];
            else
                return null;
        }

        /*
         * @method: InsertUser
         * @description: insert new User to database
         * @input: Users entity
         * @output: 1 if insert successful, 0 if error occurred
         */
        public int InsertUser(Users _user, out int autoNumber)
        {
            int result = 0;
            try
            {
                String[] columnNames = {    roleID,
                                            email,
                                            password,
                                            firtsName,
                                            lastName,
                                            gender,
                                            birthday,
                                            address,
                                            phone,
                                            secretQuestion,
                                            secretAnswer};

                Object[] values = { _user.RoleID,
                                    _user.Email,
                                    _user.Password,
                                    _user.FirstName,
                                    _user.LastName,
                                    _user.Gender,
                                    _user.Birthday,
                                    _user.Address,
                                    _user.Phone,
                                    _user.SecretQuestion,
                                    _user.SecretAnswer};

                result = BaseDAL.InsertTableWithReturnID(tableName, columnNames, values, out autoNumber);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }

        /*
         * @method: UpdateUser
         * @description: update Users information
         * @input: Users entity
         * @output: 1 if successful, 0 if error occurred
         */
        public int UpdateUser(Users _user)
        {
            int result = 0;
            try
            {
                String[] columnNames = {    roleID,
                                            password,
                                            firtsName,
                                            lastName,
                                            gender,
                                            birthday,
                                            address,
                                            phone,
                                            secretQuestion,
                                            secretAnswer};
                Object[] values = { _user.RoleID,
                                    _user.Password,
                                    _user.FirstName,
                                    _user.LastName,
                                    _user.Gender,
                                    _user.Birthday,
                                    _user.Address,
                                    _user.Phone,
                                    _user.SecretQuestion,
                                    _user.SecretAnswer};
                String[] keyColumnNames = { userID };
                Object[] keyColumnValues = { _user.UserID };
                result = UpdateTable(tableName, columnNames, values, keyColumnNames, keyColumnValues);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }

        /*
         * @method: DisableUser
         * @description: hidden a user
         * @input: userID, isEnable <FALSE if allow hidden>
         * @output: 1 if update successful, 0 if error occurred
         */
        public int DisableUser(int _userID, bool _isEnable)
        {
            int result = 0;
            try
            {
                String[] columnNames = { isEnable };
                Object[] values = { _isEnable };
                String[] keyColumnNames = { userID };
                Object[] keyColumnValues = { _userID };
                result = UpdateTable(tableName, columnNames, values, keyColumnNames, keyColumnValues);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }

        /*
         * @method: DisableUser
         * @description: hidden a user
         * @input: userName, isEnable <FALSE if allow hidden>
         * @output: 1 if update successful, 0 if error occurred
         */
        public int DisableUser(string _email, bool _isEnable)
        {
            int result = 0;
            try
            {
                String[] columnNames = { isEnable };
                Object[] values = { _isEnable };
                String[] keyColumnNames = { email };
                Object[] keyColumnValues = { _email };
                result = UpdateTable(tableName, columnNames, values, keyColumnNames, keyColumnValues);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }

        #endregion
    }
}
